--------------------------------------------------------------------------------
-- @name: Tracing SQL execution time
-- @author: dion cho
-- @note: test case for tracing SQL execution time
--------------------------------------------------------------------------------


----------------------------------------------------
-- Case 1: v$session.last_call_et

-- for long running query without fetch
-- session #1
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

select * from dual where tpack.sleep(1, 10) = 1;


-- session #2
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select /*+ ordered use_nl(y) */
	y.status,
	y.sql_id,
	(select sql_text from v$sqlarea where sql_id = y.sql_id) as sql_text,
	y.last_call_et
from 
	(select /*+ no_merge */ level 
		from dual 
		where tpack.sleep(level, 0.1) = 1 
		connect by level <= 100) x,
	(select /*+ no_merge */ status, sql_id, last_call_et
		from v$session
		where sid = &sid) y
;



-- for long running but intermittently fetching query
-- session #1
exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

select level from dual
where tpack.sleep(level, 0.01) = 1
connect by level <= 1000
;



-- session #2
col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select /*+ ordered use_nl(y) */
	y.status,
	y.sql_id,
	(select sql_text from v$sqlarea where sql_id = y.sql_id) as sql_text,
	y.last_call_et
from 
	(select /*+ no_merge */ level 
		from dual 
		where tpack.sleep(level, 0.1) = 1 
		connect by level <= 100) x,
	(select /*+ no_merge */ status, sql_id, last_call_et
		from v$session
		where sid = &sid) y
;



-- for changing query inside PL/SQL block
-- session #1
exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

declare
	v_cnt number;
begin 
	for idx in 1 .. 100 loop
		execute immediate
			'select /* first */ 1 from dual where tpack.sleep(1,0.05) = 1' into v_cnt;
			
		execute immediate
			'select /* second */ 1 from dual where tpack.sleep(1,0.05) = 1' into v_cnt;
	end loop;
end;
/


-- session #2
col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select /*+ ordered use_nl(y) */
	y.status,
	y.sql_id,
	(select sql_text from v$sqlarea where sql_id = y.sql_id) as sql_text,
	y.last_call_et
from 
	(select /*+ no_merge */ level 
		from dual 
		where tpack.sleep(level, 0.1) = 1 
		connect by level <= 100) x,
	(select /*+ no_merge */ status, sql_id, last_call_et
		from v$session
		where sid = &sid) y
;



-- session #1
-- what happpens to sql*trace
exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select level from dual
where tpack.sleep(level, 0.01) = 1
connect by level <= 1000
;

exec tpack.end_diag_trace;

select * from table(tpack.get_diag_trace(userenv('sid'),'TKPROF','sys=no'));

	
-----------------------------------------------------------------
-- 11g sql_exec_start

-- session #1
exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

select level from dual
where tpack.sleep(level, 0.01) = 1
connect by level <= 1000
;


-- session #2
create or replace function f_sysdate
return date
is
begin
	return sysdate;
end;
/

col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select /*+ ordered use_nl(y) */
	status, last_call_et,
	(select sql_text from v$sqlarea where sql_id = y.sql_id) as sql_text,
	(f_sysdate - sql_exec_start)*24*60*60) as elapsed_time
from 
	(select /*+ no_merge */ level 
		from dual 
		where tpack.sleep(level, 0.1) = 1 
		connect by level <= 150) x,
	(select /*+ no_merge */ status, sql_id, last_call_et, sql_exec_start
		from v$session
		where sid = &sid) y
;



-- for changing query inside PL/SQL block
-- session #1
exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

declare
	v_cnt number;
begin 
	for idx in 1 .. 100 loop
		execute immediate
			'select /* first */ 1 from dual where tpack.sleep(1,0.05) = 1' into v_cnt;
			
		execute immediate
			'select /* second */ 1 from dual where tpack.sleep(1,0.05) = 1' into v_cnt;
	end loop;
end;
/


col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select /*+ ordered use_nl(y) */
	status, last_call_et,
	(select sql_text from v$sqlarea where sql_id = y.sql_id) as sql_text,
	(f_sysdate - sql_exec_start)*24*60*60 as elapsed_time
from 
	(select /*+ no_merge */ level 
		from dual 
		where tpack.sleep(level, 0.1) = 1 
		connect by level <= 100) x,
	(select /*+ no_merge */ status, sql_id, last_call_et, sql_exec_start
		from v$session
		where sid = &sid) y
;




